package conversion;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Set;

import bo.BattingStats;
import bo.CatchingStats;
import bo.FieldingStats;
import bo.PitchingStats;
import bo.Player;
import bo.PlayerSeason;
import bo.Team;
import bo.TeamSeason;
import bo.TeamSeasonPlayer;
import dataaccesslayer.HibernateUtil;

public class Convert {

	static Connection conn;
	static HashMap<String, Player> players = new HashMap<String, Player>();
	static final String MYSQL_CONN_URL = "jdbc:mysql://192.168.56.1/mlb?user=admin&password=password"; 

	public static void main(String[] args) {
		try {
			long startTime = System.currentTimeMillis();
			conn = DriverManager.getConnection(MYSQL_CONN_URL);
			System.out.println("Conversion started");
			convertPlayers();
			convertTeams();
			System.out.println("Conversion finished");
			long endTime = System.currentTimeMillis();
			long elapsed = (endTime - startTime) / (1000*60);
			System.out.println("Elapsed time in mins: " + elapsed);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (!conn.isClosed()) conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	public static void convertPlayers() {
		try {
			PreparedStatement ps = conn.prepareStatement("select " + 
						"playerID, " + 
						"nameFirst, " + 
						"nameLast, " + 
						"NameNick, "+ 
						"birthDay, " + 
						"birthMonth, " + 
						"birthYear, " + 
						"deathDay, "+ 
						"deathMonth, " + 
						"deathYear, " + 
						"bats, " + 
						"throws, " + 
						"birthCity, " + 
						"birthState, " + 
						"debut, " + 
						"finalGame, " + 
						"college " +
						"from MASTER");
						// for debugging comment previous line, uncomment next line
						//"from MASTER where playerID = 'bondsba01' or playerID = 'youklke01';");
			ResultSet rs = ps.executeQuery();
			int count=0; // for progress feedback only
			while (rs.next()) {
				count++;
				// this just gives us some progress feedback
				if (count % 1000 == 0) System.out.println("num players: " + count);
				String pid = rs.getString("playerID").trim();
				String firstName = rs.getString("nameFirst").trim();
				String lastName = rs.getString("nameLast").trim();
				// this check is for data scrubbing
				// don't want to bring anybody over that doesn't have a pid, firstname and lastname
				if (pid == null	|| pid.isEmpty() || 
					firstName == null || firstName.isEmpty() ||
					lastName == null || lastName.isEmpty()) continue;
				Player p = new Player();
				p.setName(firstName + " " + lastName);
				p.setNickName(rs.getString("nameNick"));
				java.util.Date birthDay = convertIntsToDate(rs.getInt("birthYear"), rs.getInt("birthMonth"), rs.getInt("birthDay"));
				if (birthDay!=null) p.setBirthDay(birthDay);
				java.util.Date deathDay = convertIntsToDate(rs.getInt("deathYear"), rs.getInt("deathMonth"), rs.getInt("deathDay"));
				if (deathDay!=null) p.setDeathDay(deathDay);
				// need to do some data scrubbing for bats and throws columns
				String hand = rs.getString("bats").trim();
				if (hand.equalsIgnoreCase("B")) {
					hand = "S";
				} else if (hand.isEmpty()) {
					hand = null;
				}
				p.setBattingHand(hand);
				hand = rs.getString("throws").trim();
				if (hand.isEmpty()) {
					hand = null;
				}
				p.setThrowingHand(hand);
				p.setBirthCity(rs.getString("birthCity"));
				p.setBirthState(rs.getString("birthState"));
				java.util.Date firstGame = convertStringToDate(rs.getString("debut"));
				if (firstGame!=null) p.setFirstGame(firstGame);
				java.util.Date lastGame = convertStringToDate(rs.getString("finalGame"));
				if (lastGame!=null) p.setLastGame(lastGame);
				p.setCollege(rs.getString("college"));
				addPositions(p, pid);
				// players bio collected, now go after stats
				addSeasons(p, pid);
				// we can now persist player, and the seasons and stats will cascade
				HibernateUtil.persistPlayer(p);
				//System.out.println(pid + ", " + p.getId());
				players.put(pid, p);
				
			}
			rs.close();
			ps.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	
	public static void convertTeams() {
		try {
			int id = 0;
			/*  Team
				teamId			numeric(10,0)		primary key,
				name			varchar(100),
				league			varchar(100),
				yearFounded		numeric(4,0),  --should probably put restraints
				yearLast
			*/	
			PreparedStatement ps = conn.prepareStatement("select distinct " + 
					"teamIDlahman45, " + 	// teamId
					/*"name " +   	// name*/
					"lgID " +    	// league 
					"from Teams;"
			);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
			    String teamId = rs.getString("teamIDlahman45").trim();
				String lgId = rs.getString("lgID").trim();
				PreparedStatement temp = conn.prepareStatement("select distinct " + 
						"name " +    	// league 
						"from Teams where teamIDlahman45= ? AND lgID= ?;"
				);
				temp.setString(1, teamId);
				temp.setString(2, lgId);
				ResultSet tempResult = temp.executeQuery();
				tempResult.next();
				String name = tempResult.getString("name").trim();
				if (teamId == null	|| teamId.isEmpty() || 
						lgId == null || lgId.isEmpty()) continue;
				Team t = new Team();
				t.setTeamId(++id);
				t.setName(name);
				t.setLeague(lgId);
				// need to find year founded and year last (not explicit in database)
				System.out.println(name);
				PreparedStatement ns = conn.prepareStatement(
						"select yearID from Teams where lgID= ? AND teamIDlahman45= ? order by yearID asc;"
				);
				ns.setString(1, lgId);
				ns.setString(2, teamId);
				ResultSet result = ns.executeQuery();
				result.next();
				String yearFounded = result.getString("yearID").trim();
				result.last();
				String yearLast = result.getString("yearID").trim();
				t.setYearFounded(Integer.parseInt(yearFounded));
				t.setYearLast(Integer.parseInt(yearLast));
				// TeamSeason
				addTeamSeasons(t, teamId, lgId);
				// TeamSeasonPlayer
				addTeamSeasonPlayers(t, teamId, lgId);
				// Hand to hibernate
				HibernateUtil.persistTeam(t);
				System.out.println("team added");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	
	private static java.util.Date convertIntsToDate(int year, int month, int day) {
		Calendar c = new GregorianCalendar();
		java.util.Date d=null;
		// if year is 0, then date wasn't populated in MySQL database
		if (year!=0) {
			c.set(year, month, day);
			d = c.getTime();
		}
		return d;
	}
	
	private static java.util.Date convertStringToDate(String dateString) {
		Calendar c = new GregorianCalendar();
		java.util.Date d=null;
		if (!(dateString == null || dateString.isEmpty())) {
			String parts[] = dateString.split("/");
			c.set(Integer.parseInt(parts[2]),
					Integer.parseInt(parts[0]),
					Integer.parseInt(parts[1]));
			d = c.getTime();
		}		
		return d;
	}
	
	public static void addPositions(Player p, String pid) {
		Set<String> positions = new HashSet<String>();
		try {
			PreparedStatement ps = conn.prepareStatement("select " +
					"distinct pos " +
					"from fielding " +
					"where playerID = ?;");
			ps.setString(1, pid);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				String pos = rs.getString("pos");
				positions.add(pos);
			}
			rs.close();
			ps.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		p.setPositions(positions);
	}

	public static void addSeasons(Player p, String pid) {
		try {
			PreparedStatement ps = conn.prepareStatement("select " + 
					"yearID, " + 
					"teamID, " +
					"lgId, " +
					"sum(G) as gamesPlayed " + 
					"from batting " + 
					"where playerID = ? " + 
					"group by yearID, teamID, lgID;");
			ps.setString(1, pid);
			ResultSet rs = ps.executeQuery();
			PlayerSeason s = null;
			while (rs.next()) {
				int yid = rs.getInt("yearID");
				s = p.getPlayerSeason(yid);
				// it is possible to see more than one of these per player if he switched teams
				// set all of these attrs the first time we see this playerseason
				if (s==null) {
					s = new PlayerSeason(p,yid);
					p.addSeason(s);
					s.setGamesPlayed(rs.getInt("gamesPlayed"));
					double salary = getSalary(pid, yid);
					s.setSalary(salary);
					BattingStats batting = getBatting(s,pid,yid);
					s.setBattingStats(batting);
					FieldingStats fielding = getFielding(s,pid,yid);
					s.setFieldingStats(fielding);
					PitchingStats pitching = getPitching(s,pid,yid);
					s.setPitchingStats(pitching);
					CatchingStats catching = getCatching(s,pid,yid);
					s.setCatchingStats(catching);
				// set this the consecutive time(s) so it is the total games played regardless of team	
				} else {
					s.setGamesPlayed(rs.getInt("gamesPlayed")+s.getGamesPlayed());
				}
			}
			rs.close();
			ps.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	
	public static void addTeamSeasons(Team t, String tid, String lid) {
		try {
			/*  TeamSeason
			    teamId			numeric(10,0)		primary key references Team,
				year			numeric(4,0)		primary key,  --should probably put restraints
				gamesPlayed		numeric(3,0),
				wins			numeric(3,0),
				losses			numeric(3,0),
				rank			numeric(4,0),
				totalAttendance numeric(10,0) );
			 */
			PreparedStatement ps = conn.prepareStatement("select " + 
					"yearID, " + 	// year
					"teamIDlahman45, " + 	// teamId
					"G, " +			// gamesPlayed
					"W, " + 		// wins
					"L, " +			// losses
					"Rank, " +		// rank
					"attendance " + // attendance
					"from Teams " + 
					"where teamIDlahman45 = ? AND lgID = ?" + 
					";");
			ps.setString(1, tid);
			ps.setString(2, lid);
			ResultSet rs = ps.executeQuery();
			TeamSeason s = null;
			while (rs.next()) {
				int yid = rs.getInt("yearID");
				s = t.getTeamSeason(yid);
				if (s==null) {
					s = new TeamSeason(t,yid);
					t.addSeason(s);
					s.setYear(Integer.parseInt(rs.getString("yearID")));
					s.setGamesPlayed(Integer.parseInt(rs.getString("G")));
					s.setWins(Integer.parseInt(rs.getString("W")));
					s.setLosses(Integer.parseInt(rs.getString("L")));
					s.setRank(Integer.parseInt(rs.getString("Rank")));
					s.setTotalAttendance(Integer.parseInt(rs.getString("attendance")));
				} 
			}
			rs.close();
			ps.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	
	public static void addTeamSeasonPlayers(Team t, String teamId, String leagueId) {
		try {
			/*  TeamSeasonPlayer
			    teamId
			    playerId
			    year
			 */
			PreparedStatement ps = conn.prepareStatement("select " + 
					"yearID, playerID from salaries where teamID = ? and lgID = ?" +
					";");
			ps.setString(1, teamId);
			ps.setString(2, leagueId);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				TeamSeasonPlayer sp = null;
				int yid = rs.getInt("yearID");
				Player p = getPlayer(rs.getString("playerID"));
				sp = t.getTeamSeasonPlayer(yid, p);
				if (sp==null && p != null) {
					sp = new TeamSeasonPlayer(t, yid, p);
					t.addSeasonPlayer(sp);
				} 
			}
			rs.close();
			ps.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static Player getPlayer(String id) {
		return players.get(id);
	}

	public static double getSalary(String pid, Integer yid) {
		double salary = 0;
		try {
			PreparedStatement ps = conn.prepareStatement("select " + 
					"sum(salary) as salary " + 
					"from salaries " + 
					"where playerID = ? " + 
					"and yearID = ? ;");
			ps.setString(1, pid);
			ps.setInt(2, yid);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				salary = rs.getDouble("salary");
			}
			rs.close();
			ps.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return salary;
	}

	public static BattingStats getBatting(PlayerSeason psi, String pid, Integer yid) {
		BattingStats s = new BattingStats();
		try {
			PreparedStatement ps = conn.prepareStatement("select "	+ "" +
					"sum(AB) as atBats, " + 
					"sum(H) as hits, " + 
					"sum(2B) as doubles, " + 
					"sum(3B) as triples, " + 
					"sum(HR) as homeRuns, " + 
					"sum(RBI) as runsBattedIn, " + 
					"sum(SO) as strikeouts, " + 
					"sum(BB) as walks, " + 
					"sum(HBP) as hitByPitch, " + 
					"sum(IBB) as intentionalWalks, " + 
					"sum(SB) as steals, " + 
					"sum(CS) as stealsAttempted " + 
					"from batting " + 
					"where playerID = ? " + 
					"and yearID = ? ;");
			ps.setString(1, pid);
			ps.setInt(2, yid);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				s.setId(psi);
				s.setAtBats(rs.getInt("atBats"));
				s.setHits(rs.getInt("hits"));
				s.setDoubles(rs.getInt("doubles"));
				s.setTriples(rs.getInt("triples"));
				s.setHomeRuns(rs.getInt("homeRuns"));
				s.setRunsBattedIn(rs.getInt("runsBattedIn"));
				s.setStrikeouts(rs.getInt("strikeouts"));
				s.setWalks(rs.getInt("walks"));
				s.setHitByPitch(rs.getInt("hitByPitch"));
				s.setIntentionalWalks(rs.getInt("intentionalWalks"));
				s.setSteals(rs.getInt("steals"));
				s.setStealsAttempted(rs.getInt("stealsAttempted"));
			}
			rs.close();
			ps.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return s;
	}
	
	public static FieldingStats getFielding(PlayerSeason psi, String pid, Integer yid) {
		FieldingStats s = new FieldingStats();
		try {
			PreparedStatement ps = conn.prepareStatement("select " +
					"sum(E) as errors, " +
					"sum(PO) as putOuts " +
					"from fielding " +
					"where playerID = ? " + 
					"and yearID = ? ;");
			ps.setString(1, pid);
			ps.setInt(2, yid);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				s.setId(psi);
				s.setErrors(rs.getInt("errors"));
				s.setPutOuts(rs.getInt("putOuts"));
			}
			rs.close();
			ps.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return s;
	}
	
	public static PitchingStats getPitching(PlayerSeason psi, String pid, Integer yid) {
		PitchingStats s = new PitchingStats();
		try {
			PreparedStatement ps = conn.prepareStatement("select " +
					"sum(IPOuts) as outsPitched, " + 
					"sum(ER) as earnedRunsAllowed, " +
					"sum(HR) as homeRunsAllowed, " + 
					"sum(SO) as strikeouts, " +
					"sum(BB) as walks, " + 
					"sum(W) as wins, " +
					"sum(L) as losses, " + 
					"sum(WP) as wildPitches, " +
					"sum(BFP) as battersFaced, " + 
					"sum(HBP) as hitBatters, " +
					"sum(SV) as saves " + 
					"from pitching " +
					"where playerID = ? " + 
					"and yearID = ? ;");
			ps.setString(1, pid);
			ps.setInt(2, yid);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				s.setId(psi);
				s.setOutsPitched(rs.getInt("outsPitched"));
				s.setEarnedRunsAllowed(rs.getInt("earnedRunsAllowed"));
				s.setHomeRunsAllowed(rs.getInt("homeRunsAllowed"));
				s.setStrikeouts(rs.getInt("strikeouts"));
				s.setWalks(rs.getInt("walks"));
				s.setWins(rs.getInt("wins"));
				s.setLosses(rs.getInt("losses"));
				s.setWildPitches(rs.getInt("wildPitches"));
				s.setBattersFaced(rs.getInt("battersFaced"));
				s.setHitBatters(rs.getInt("hitBatters"));
				s.setSaves(rs.getInt("saves"));
			}
			rs.close();
			ps.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return s;
	}
	
	public static CatchingStats getCatching(PlayerSeason psi, String pid, Integer yid) {
		CatchingStats s = new CatchingStats();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement("select " +
					"sum(PB) as passedBalls, " +
					"sum(WP) as wildPitches, " +
					"sum(SB) as stealsAllowed, " +
					"sum(CS) as stealsCaught " +
					"from fielding " +
					"where playerID = ? " + 
					"and yearID = ? ;");
			ps.setString(1, pid);
			ps.setInt(2, yid);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				s.setId(psi);
				s.setPassedBalls(rs.getInt("passedBalls"));
				s.setWildPitches(rs.getInt("wildPitches"));
				s.setStealsAllowed(rs.getInt("stealsAllowed"));
				s.setStealsCaught(rs.getInt("stealsCaught"));
			}
			rs.close();
			ps.close();
		} catch (Exception e) {
			ps.toString();
			e.printStackTrace();
		}
		return s;
	}

}